import { BillCodeGenerator } from './../service/tool.service';
import {
  PersonQueryCondition,
  PersonQueryConditionEx,
  SomeBill,
} from './../entity/train.entity';
import { MissTargetError } from './../filter/missTarget.filter';
import {
  Body,
  Controller,
  Del,
  Get,
  Inject,
  Param,
  Post,
  Put,
} from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository } from 'typeorm';
import { Person } from '../entity/train.entity';

@Controller('/person')
export class PersonController {
  @InjectEntityModel(Person)
  repoPerson: Repository<Person>;
  @Post('/insert')
  async insert(@Body() person: Person) {
    console.log(person);
    person.personId = 0;
    // 新增保存前 需要进行初始化 例如 id设置 例如状态设置 “” -1
    return await this.repoPerson.save(person);
  }
  @Post('/query')
  async query(): Promise<Array<Person>> {
    // select * from person
    const r = await this.repoPerson.findBy({});
    // findBy 返回可能是 [] [{name: "John", age:10}] [...]
    // findOneBy 返回是实体 entity | null
    console.log(r);
    return r;
  }
  @Post('/queryEx')
  async queryEx(@Body() qc: PersonQueryConditionEx) {
    const { pageNum, pageSize, minAge, maxAge, nameLike } = qc;
    const pageStart = (pageNum - 1) * pageSize;
    const builder = this.repoPerson.createQueryBuilder('p');
    // 跳转到数据库 写sql语句
    // select *        FROM Person p WHERE p.name like "%a%" and p.age > 18 and p.age<100
    // ORDER BY p.personId desc LIMIT 0,3;
    // SELECT count(1) from Person p WHERE p.name like "%a%" and p.age > 18 and p.age<100;
    if (nameLike) {
      builder.andWhere('p.name like :n', { n: '%' + nameLike + '%' });
    }
    if (minAge) {
      builder.andWhere(' p.age > :minAge ', { minAge });
    }
    if (maxAge) {
      builder.andWhere(' p.age < :maxAge ', { maxAge });
    }
    builder.orderBy('p.personId', 'DESC').offset(pageStart).limit(pageSize);
    // const r = builder.printSql().getMany();
    const r = await builder.printSql().getManyAndCount();
    return {
      results: r[0],
      total: r[1],
    };
  }
  @Post('/queryExr')
  async queryExr(@Body() qc: PersonQueryConditionEx) {
    const { pageNum, pageSize, minAge, maxAge, nameLike } = qc;
    const pageStart = (pageNum - 1) * pageSize;
    const builder = this.repoPerson.createQueryBuilder('p');
    const builder4Count = this.repoPerson.createQueryBuilder('p');
    // 跳转到数据库 写sql语句
    // select *        FROM Person p WHERE p.name like "%a%" and p.age > 18 and p.age<100
    // ORDER BY p.personId desc LIMIT 0,3;
    // SELECT count(1) as value from Person p WHERE p.name like "%a%" and p.age > 18 and p.age<100;

    if (nameLike) {
      builder.andWhere('p.name like :n', { n: '%' + nameLike + '%' });
      builder4Count.andWhere('p.name like :n', { n: '%' + nameLike + '%' });
    }
    if (minAge) {
      builder.andWhere(' p.age > :minAge ', { minAge });
      builder4Count.andWhere(' p.age > :minAge ', { minAge });
    }
    if (maxAge) {
      builder.andWhere(' p.age < :maxAge ', { maxAge });
      builder4Count.andWhere(' p.age < :maxAge ', { maxAge });
    }
    builder.orderBy('p.personId', 'DESC').offset(pageStart).limit(pageSize);
    builder4Count.select('count(1)', 'value');
    // const r = builder.printSql().getMany();
    const r = await builder.printSql().getMany();
    const t = await builder4Count.printSql().getRawOne();
    console.log(r, t);
    return {
      results: r,
      total: Number(t.value),
    };
  }

  @Post('/query1')
  async query1() {
    // 看第二页 每页三条
    const pageNum = 2;
    const pageSize = 3;
    const pageStart = (pageNum - 1) * pageSize;
    const re = await this.repoPerson.query(
      `SELECT * from Person p WHERE p.age >18 
      ORDER BY p.birthday desc limit ${pageStart},${pageSize}`
    );
    const re2 = await this.repoPerson.query(
      'SELECT * from Person p WHERE p.age >18 ORDER BY p.birthday desc limit ?,?',
      [pageStart, pageSize]
    );
    // 多条件动态查询（基础分页）建议使用如下api
    // select * from Person p
    const builder = this.repoPerson.createQueryBuilder();
    // builder.select().from().where().orderBy().offset().limit()
    builder.select('p');
    // builder.select(['p,age','p.name']);
    builder.from(Person, 'p');
    builder.where('p.age > :minAge', { minAge: 18 });
    builder.orderBy('p.birthday', 'DESC').offset(pageStart).limit(pageSize);
    const re3 = await builder.printSql().getRawMany();
    // const re3 = await builder.printSql().getMany();
    // resultmap 结果集映射
    // builder.
    // builder
    const rt = await this.repoPerson.query(
      "select count(1) as 'geshu',avg(p.age) as 'pingjunnianling' from Person p"
    );
    const cb2 = this.repoPerson.createQueryBuilder('p');
    cb2.select('count(*)', 'geshu');
    const rt2 = await cb2.printSql().getRawMany();
    const rt3 = await cb2.printSql().getRawOne();
    const rt4 = await cb2.printSql().getMany();
    const rt5 = await cb2.printSql().getOne();
    console.log(re);
    console.log(re2);
    console.log('---------', re3);
    console.log(rt);
    console.log(rt2);
    console.log(rt3);
    console.log(rt4);
    console.log(rt5);
    console.log('---------');
  }
  @Post('/query2')
  async query2(@Body() qc: PersonQueryCondition) {
    // const pageNum = qc.pageNum;
    // const pageSize = qc.pageSize;
    // const minAge = qc.minAge;
    // 解构赋值
    const { pageNum, pageSize, minAge } = qc;
    const pageStart = (pageNum - 1) * pageSize;
    /* 'SELECT * from Person p WHERE p.age >18 ORDER BY p.birthday desc limit ?,?', */
    const cb = this.repoPerson.createQueryBuilder('p');
    cb.where('p.age > :minAge', { minAge });
    cb.orderBy('p.birthday', 'DESC').offset(pageStart).limit(pageSize);
    const results = await cb.printSql().getMany();
    // 调试必用 const results = await cb.printSql().getRawMany();
    console.log(results);
    // "select count(1) as 'geshu' from Person p where p.age >18 "
    const cbCount = this.repoPerson.createQueryBuilder('p');
    cbCount.select('count(1)', 'value');
    cbCount.where('p.age > :minAge', { minAge });
    const c: any = await cbCount.printSql().getRawOne();
    console.log(c);

    return {
      results: results,
      total: Number(c.value),
    };
  }
  @Post('/query3')
  async query3(@Body() qc: PersonQueryCondition) {
    const { maxAge, minAge, nameLike } = qc;
    // 至少两个条件
    // select * from Person p WHERE p.`name` like "%a%" and p.age >18 and p.age< 100;
    const builder = this.repoPerson.createQueryBuilder('p');
    // 如果某用户输入条件存在不为空 则添加
    if (maxAge !== 0) {
      builder.andWhere('p.age < :maxAge', { maxAge });
    }
    if (minAge) {
      builder.andWhere('p.age > :minAge', { minAge });
    }
    if (nameLike) {
      builder.andWhere('p.`name` like :n', { n: `%${nameLike}%` });
    }
    builder.orderBy('p.personId', 'ASC').limit(10);
    const t = await builder.printSql().getRawMany();
    console.log(t);
    return t;
  }

  @Get('/find/:id')
  async findById(@Param('id') personId: number): Promise<Person> {
    // select * from person where personId = id;
    // const person = await this.repoPerson.findOneBy({personId: id});

    const person = await this.repoPerson.findOneBy({ personId });
    console.log(person);
    if (!person) {
      throw new MissTargetError('数据不存在');
    }
    return person;
  }
  @Put('/modify')
  async modify(@Body() person: Person) {
    // update person  set xxx = xxx  yyy =yyyy zzz=zzzzwhere id = 1
    // 修改 = 先查再保存
    const personFromDB = await this.repoPerson.findOneBy({
      personId: person.personId,
    });
    if (!personFromDB) {
      throw new MissTargetError('待修改的数据不存在: ' + person.personId);
    }
    console.log(person, personFromDB);
    return await this.repoPerson.save(person);
  }
  @Del('/remove/:id')
  async remove(@Param('id') personId: number) {
    const person = await this.repoPerson.findOneBy({
      personId,
    });
    if (!person) {
      throw new MissTargetError('待删除的数据不存在: ' + personId);
    }
    await this.repoPerson.remove(person);
    //  await this.repoPerson.delete(personId);
    return personId;
  }
}
@Controller('/someBill')
export class SomeBillController {
  @Inject()
  billCodeGenerator: BillCodeGenerator;
  @InjectEntityModel(SomeBill)
  someBillRepo: Repository<SomeBill>;
  @Post('/insert')
  async insert(@Body() someBill: SomeBill): Promise<SomeBill> {
    console.log(someBill);

    // 初始化设置 id;
    someBill.someBillId = this.billCodeGenerator.nextCode();
    console.log(someBill);
    // 保存到数据库;
    this.someBillRepo.save(someBill);
    return someBill;
  }
}
